import logging
import pymysql
from config.config import mysql_cfg


class PyMySql:
    # 初始化数据库链接
    def __init__(self):
        self.db = pymysql.connect(
            host=mysql_cfg['host'],
            user=mysql_cfg['user'],
            password=mysql_cfg['password'],
            port=int(mysql_cfg['port']),
            database=mysql_cfg['database']
        )
        # 建立数据库游标
        self.cursor = self.db.cursor()

    # 查询数据库
    def executebase(self):
        self.cursor.execute('SHOW DATABASES;')
        return self.cursor.fetchall()

    # 查询表
    def executeall(self, sql, params=None):
        with self.db.cursor() as cursor:
            cursor.execute(sql)
            return cursor.fetchall()

    # 查询表的的第一个数据
    def executeone(self, sql):
        with self.db.cursor() as cursor:
            cursor.execute(sql)
            return cursor.fetchone()

    # 修改表
    def exec(self, sql):
        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                self.db.commit()
            return True
        except Exception as ex:
            self.db.rollback()
            logging.error(f"执行SQL失败: {sql}")
            logging.error(ex)
            logging.error('mysql执行失败')
            return False

    def execute_delete(self, sql, params=None):
        """执行删除/更新操作，返回受影响的行数"""
        with self.db.cursor() as cursor:
            cursor.execute(sql, params or ())
            self.db.commit()  # 确保提交事务
            return cursor.rowcount

    # 关闭游标对象
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.db and self.db.open:
            self.db.close()

